﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace XemlUtilities.ISA
{
    class TagFinder
    {
        public static MemoryStream GetStream(Guid xemlId, string connectionstring)
        {
            MemoryStream res = new MemoryStream();

            System.IO.StreamWriter txtWriter = new System.IO.StreamWriter(res);
            txtWriter.WriteLine(string.Concat("TagFinder file ID", "\t", "parameter name", "\t", "value"));

            foreach (var item in GetTagFileDetails(xemlId, connectionstring))
            {
                txtWriter.WriteLine(string.Concat(item.Item1, "\t", item.Item2, "\t", item.Item3));
            }
            txtWriter.Flush();
            res.Seek(0, SeekOrigin.Begin);
            return res;
        }

        private static IEnumerable<Tuple<Guid, string, string>> GetTagFileDetails(Guid xemlId, string connectionString)
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            using (cmd.Connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                cmd.CommandText = @"
with 
xemlpart as(
SELECT   distinct     tf.IntensityValue.FK_TagList as taglist
FROM            tf.IntensityValue
		INNER JOIN GC_Chromatogram ON tf.IntensityValue.FK_chromatogram = GC_Chromatogram.id AND tf.IntensityValue.FK_chromatogram = GC_Chromatogram.id
		INNER JOIN xeml.SampleMapping ON GC_Chromatogram.name = xeml.SampleMapping.Value
		INNER JOIN xeml.[Document] ON xeml.SampleMapping.FK_Document = xeml.[Document].Id
WHERE        (xeml.[Document].XemlId = @xemlId) AND (xeml.SampleMapping.SubKey = 'Chromatogram')
)
SELECT        taglist, tf.TagListFileInfo.name, tf.TagListFileInfo.value
FROM            xemlpart
		INNER JOIN tf.TagListFile ON xemlpart.taglist = tf.TagListFile.FK_TagList
		INNER JOIN tf.TagListFileInfo ON tf.TagListFile.id = tf.TagListFileInfo.FK_TagListFile AND tf.TagListFile.id = tf.TagListFileInfo.FK_TagListFile 
order by name;";
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@xemlId", xemlId);

                try
                {
                    cmd.Connection.Open();
                    cmd.Transaction = cmd.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
                    using (SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
                    {
                        while (reader.Read())
                        {
                            yield return new Tuple<Guid, string, string>(reader.GetGuid(0), reader.GetString(1), reader.GetString(2));
                        }
                        yield break;
                    }
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }
    }
}
